﻿#if !NETSTANDARD2_1
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace Dapper.Sharding
{
    internal class SapHanaDatabase : IDatabase
    {
        public SapHanaDatabase(string name, IClient client) : base(name, client)
        {
            ConnectionString = ConnectionStringBuilder.BuilderSapHana(client.Config, name.ToUpper());
        }

        public override string ConnectionString { get; set; }

        public override void DropTable(string name)
        {
            Execute($"DROP TABLE {name.ToUpper()}");
        }

        public override bool ExistsTable(string name)
        {
            var sql = $"SELECT COUNT(1) FROM TABLES WHERE SCHEMA_NAME='{Name.ToUpper()}' AND TABLE_NAME='{name.ToUpper()}'";
            return ExecuteScalar<bool>(sql);
        }

        public override async Task<bool> ExistsTableAsync(string name)
        {
            var sql = $"SELECT COUNT(1) FROM TABLES WHERE SCHEMA_NAME='{Name.ToUpper()}' AND TABLE_NAME='{name.ToUpper()}'";
            return await ExecuteScalarAsync<bool>(sql);
        }

        public override DbConnection GetConn()
        {
            var conn = new Sap.Data.Hana.HanaConnection(ConnectionString);
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch
                {
                    conn.Dispose();
                    throw;
                }
            }
            return conn;
        }

        public override async Task<DbConnection> GetConnAsync()
        {
            var conn = new Sap.Data.Hana.HanaConnection(ConnectionString);
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    await conn.OpenAsync();
                }
                catch
                {
#if CORE6
                    await conn.DisposeAsync();
#else
                    conn.Dispose();
#endif
                    throw;
                }
            }
            return conn;
        }

        public override List<string> GetTableColumnList(string name)
        {
            var sql = $"SELECT COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME='{Name.ToUpper()}' AND TABLE_NAME='{name.ToUpper()}' ORDER BY POSITION";
            return Query<string>(sql);
        }

        public override async Task<List<string>> GetTableColumnListAsync(string name)
        {
            var sql = $"SELECT COLUMN_NAME FROM TABLE_COLUMNS WHERE SCHEMA_NAME='{Name.ToUpper()}' AND TABLE_NAME='{name.ToUpper()}' ORDER BY POSITION";
            return await QueryAsync<string>(sql);
        }

        public override TableEntity GetTableEntityFromDatabase(string name, bool firstCharToUpper = false)
        {
            var entity = new TableEntity();

            var comSql = $"SELECT COMMENTS FROM TABLES WHERE SCHEMA_NAME='{Name.ToUpper()}' AND TABLE_NAME='{name.ToUpper()}'";
            entity.Comment = ExecuteScalar<string>(comSql);
            entity.PrimaryKey = "";
            var manager = GetTableManager(name);

            entity.IndexList = manager.GetIndexEntityList();
            entity.ColumnList = manager.GetColumnEntityList(null, firstCharToUpper);

            var ix = entity.IndexList.FirstOrDefault(f => f.Type == IndexType.PrimaryKey);
            if (ix != null)
            {
                entity.PrimaryKey = ix.Columns.FirstCharToUpper();
            }

            if (entity.PrimaryKey != null)
            {
                var col = entity.ColumnList.FirstOrDefault(w => w.Name.ToLower() == entity.PrimaryKey.ToLower());
                if (col != null)
                {
                    entity.PrimaryKeyType = col.CsType;
                    entity.IsIdentity = col.IsIdentity;
                }
            }

            return entity;
        }

        public override List<string> GetTableList()
        {
            var sql = $"SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME='{Name.ToUpper()}'";
            return Query<string>(sql);
        }

        public override ITableManager GetTableManager(string name)
        {
            return new SapHanaTableManager(name, this);
        }

        public override string GetTableScript<T>(string name)
        {
            var tableEntity = ClassToTableEntityUtils.Get<T>(DbType, DbVersion);
            var sb = new StringBuilder();
            if (string.IsNullOrEmpty(tableEntity.Engine))
            {
                sb.Append($"CREATE TABLE {name.ToUpper()} (");
            }
            else
            {
                sb.Append($"CREATE {tableEntity.Engine} TABLE {name.ToUpper()} (");
            }

            foreach (var item in tableEntity.ColumnList)
            {
                sb.Append($"{item.Name.ToUpper()} {item.DbType}");
                if (tableEntity.PrimaryKey.ToLower() == item.Name.ToLower())
                {
                    sb.Append(" PRIMARY KEY");

                    if (tableEntity.IsIdentity)
                    {
                        sb.Append(" GENERATED ALWAYS AS IDENTITY");
                    }
                }
                else
                {
                    if (item.CsType.IsValueType && item.CsType != typeof(DateTime) && item.CsType != typeof(DateTimeOffset) && item.CsType != typeof(DateTime?) && item.CsType != typeof(DateTimeOffset?))
                    {
                        if (item.CsType != typeof(bool))
                        {
                            sb.Append(" DEFAULT 0");
                        }
                        else
                        {
                            sb.Append(" DEFAULT FALSE");
                        }
                    }
                }

                if (item != tableEntity.ColumnList.Last())
                {
                    sb.Append(",");
                }
            }

            sb.Append(")");
            return sb.ToString();
        }

        public override void OptimizeTable(string name, bool final = false, bool deduplicate = false)
        {
            throw new NotImplementedException();
        }

        public override void OptimizeTable(string name, string partition, bool final = false, bool deduplicate = false)
        {
            throw new NotImplementedException();
        }

        public override void SetTableComment(string name, string comment)
        {
            throw new NotImplementedException();
        }

        public override void SetTablespace(string table, string tablespace)
        {
            throw new NotImplementedException();
        }

        public override Task SetTablespaceAsync(string table, string tablespace)
        {
            throw new NotImplementedException();
        }

        public override void TruncateTable(string name)
        {
            Execute($"TRUNCATE TABLE {name.ToUpper()}");
        }

        public override async Task TruncateTableAsync(string name, CancellationToken cancellationToken = default)
        {
            await ExecuteAsync($"TRUNCATE TABLE {name.ToUpper()}", cancellationToken: cancellationToken);
        }

        public override void Vacuum()
        {
            throw new NotImplementedException();
        }

        protected override ITable<T> CreateITable<T>(string name)
        {
            return new SapHanaTable<T>(name, this);
        }

        public override void CreateTable<T>(string name)
        {
            var script = GetTableScript<T>(name);
            var tableEntity = ClassToTableEntityUtils.Get<T>(DbType, DbVersion);
            using var cnn = GetConn();
            using var tran = cnn.BeginTransaction();
            try
            {
                cnn.Execute(script, transaction: tran);

                foreach (var item in tableEntity.IndexList)
                {
                    if (item.Type == IndexType.Normal)
                    {
                        cnn.Execute($"CREATE INDEX {Name.ToUpper()}.\"{name}_{item.Name}\" ON {Name.ToUpper()}.\"{name.ToUpper()}\" ({item.Columns})", transaction: tran);
                    }
                    else if (item.Type == IndexType.Unique)
                    {
                        cnn.Execute($"CREATE UNIQUE INDEX {Name.ToUpper()}.\"{name}_{item.Name}\" ON {Name.ToUpper()}.\"{name.ToUpper()}\" ({item.Columns})", transaction: tran);
                    }
                }


                if (!string.IsNullOrEmpty(tableEntity.Comment))
                {
                    var sql = $"COMMENT ON TABLE {name.ToUpper()} IS '{tableEntity.Comment}'";
                    cnn.Execute(sql, transaction: tran);
                }

                foreach (var item in tableEntity.ColumnList)
                {
                    if (!string.IsNullOrEmpty(item.Comment))
                    {
                        var sql = $"COMMENT ON COLUMN {name.ToUpper()}.{item.Name.ToUpper()} IS '{item.Comment}'";
                        cnn.Execute(sql, transaction: tran);
                    }
                }

                tran.Commit();
            }
            catch
            {
                tran.Rollback();
                throw;
            }
        }

    }
}
#endif